﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.IO;
using System.Collections;
using System.Globalization;

using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace StockMentor
{
    public partial class frmImport : Form
    {
        private static SqlConnection connStatic;
        private static Form _singletonInstance = null;
        private SqlConnection conn;
        private Form formCalling;
        const int m_MaxDataCols = 5;
        Hashtable htFields = new Hashtable();

        public frmImport(SqlConnection conn)
        {
            InitializeComponent();
            CustomProcedures.conn = conn;
            this.conn = conn;
        }

        public static Form GetSingletonInstance(SqlConnection conection)
        {
            if (_singletonInstance == null || _singletonInstance.IsDisposed)
            {
                connStatic = conection;
                _singletonInstance = new frmImport(connStatic);
            }
            return _singletonInstance;
        }

        public void SetKeyValues(Form formCalling)
        {
            this.formCalling = formCalling;
        }
       

        private void btnFisier_Click(object sender, System.EventArgs e)
        {
            if (folderBrowserDialog1.ShowDialog() == DialogResult.OK)
            {
                txtPath.Text = folderBrowserDialog1.SelectedPath;
            }
            
            /*this.saveFileDialog1.FileName = System.DateTime.Today.ToShortDateString() + ".xls";
            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                txtPath.Text = this.saveFileDialog1.FileName;
            }*/
        }


        private void btnDescarca_Click(object sender, System.EventArgs e)
        {
            CultureInfo ciEn = new CultureInfo("en-US", true);
            
            bool toReturn = false;
            Microsoft.Office.Interop.Excel.Application oExcel = null;
            Microsoft.Office.Interop.Excel.Workbook oBook = null;
            Microsoft.Office.Interop.Excel.Worksheet oSheet = null;

            //txtPath.Text = this.saveFileDialog1.FileName.ToString();
            this.saveFileDialog1.FileName = txtPath.Text.Trim();
            this.saveFileDialog1.Filter = "|.xls";
            this.saveFileDialog1.Title = "Salvare macheta stocuri in formatul Excell";

            string sFile = txtPath.Text.Trim(); //, sFileName = System.DateTime.Today.ToShortDateString() + ".xls";
            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    txtPath.Text = saveFileDialog1.FileName;
                    Path.GetFullPath(txtPath.Text);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Introduceti o cale pentru fisier!", "", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    txtPath.Focus();
                    return;
                }

                if (oExcel == null) oExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();


                oBook = oExcel.Workbooks.Add(1);
                oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oBook.Sheets[1];

                //row, col
                oSheet.Cells[1, 1] = "DENUMIRE PRODUS";
                oSheet.Cells[1, 2] = "PRET UNITAR VANZARE FARA TVA (RON)";
                oSheet.Cells[1, 3] = "COTA TVA";
                oSheet.Cells[1, 4] = "CANTITATE";
                oSheet.Cells[1, 5] = "UM";

                Microsoft.Office.Interop.Excel.Range oSheet_range = null;
                oSheet_range = oSheet.get_Range("A1", "E1");

                oSheet_range.Interior.Color = System.Drawing.Color.LightBlue .ToArgb();
                oSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();

                oSheet_range.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                oSheet_range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;

                oSheet_range = oSheet.get_Range("B1", "B1");
                oSheet_range.Cells.ColumnWidth = 50;

                oSheet_range = oSheet.get_Range("A1", "A1");
                oSheet_range.Cells.ColumnWidth = 20;

                oSheet_range = oSheet.get_Range("C1", "E1");
                oSheet_range.Cells.ColumnWidth = 20;

                oSheet_range = oSheet.get_Range("C1", "C1");
                oSheet_range.NumberFormat = "#,##0";

                oSheet_range = oSheet.get_Range("E1", "E1");
                oSheet_range.NumberFormat = "#,##0";

                oSheet.Cells[2, 1] = "Fax Canon JX510";
                oSheet.Cells[2, 2] = "150,20";
                oSheet.Cells[2, 3] = "19";
                oSheet.Cells[2, 4] = "5";
                oSheet.Cells[2, 5] = "buc";

                sFile = txtPath.Text;
                File.Delete(sFile);

                oBook.SaveAs(sFile, Type.Missing, Type.Missing, Type.Missing,
                                 Type.Missing, Type.Missing, XlSaveAsAccessMode.xlShared,
                     Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);


                if (chkAutomat.Checked) oExcel.Visible = true;//oSheet.Visible = XlSheetVisibility.xlSheetVisible ;
                
                //oExcel.Quit();
            }
             
        }

        private bool IsEmptyRow(Microsoft.Office.Interop.Excel.Worksheet oSheet, int iLine)
        {
            bool toReturn = true;
            for (int i = 1; i <= m_MaxDataCols; i++)
            {
                Microsoft.Office.Interop.Excel.Range rng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[iLine, i];
                if (rng.Value2 != null && rng.Value2.ToString().Trim().Length > 0)
                {
                    toReturn = false;
                    break;
                }
            }

            return toReturn;
        }

        private void btnImport_Click(object sender, System.EventArgs e)
        {
            int iCurrentLine = 1;
            bool bError = false;

            try
            {
                Path.GetFullPath(txtPath.Text);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Introduceti o cale valida pentru fisier!", "", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                txtPath.Focus();
                return;
            }

            this.openFileDialog1.Filter = "|.xls";
            this.openFileDialog1.Title = "Selectati macheta Excell";


                Microsoft.Office.Interop.Excel.Application oExcel = null;
                Microsoft.Office.Interop.Excel.Worksheet oSheet = null;
                Microsoft.Office.Interop.Excel.Workbook oBook = null;

                string sComment = string.Empty;

                string sFile = txtPath.Text.Trim();

                string sLogFile = Directory.GetDirectoryRoot(txtPath.Text) + "LOG_" + Path.GetFileNameWithoutExtension(txtPath.Text) + ".LOG";

                InitLog(sLogFile);

                if (oExcel == null) oExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
                oBook = oExcel.Workbooks.Open(sFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oBook.Sheets[1];
                if (oSheet == null) return;

                // read the headers of the worksheet, to make a list of fields
                for (int i = 1; i <= m_MaxDataCols; i++)
                {
                    string FieldName = getCellHeader(oSheet, iCurrentLine, i).ToUpper();//getCellText
                    switch (FieldName)
                    {
                        case "DENUMIRE PRODUS":
                            break;
                        case "PRET UNITAR VANZARE FARA TVA (RON)":
                            break;
                        case "COTA TVA":
                            break;
                        case "CANTITATE":
                            break;
                        case "UM":
                            break;
                        default:
                            MessageBox.Show("Formatul fisierului nu este acceptat!", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            return;
                            break;

                    }
                    if (FieldName.Length > 0 && !htFields.ContainsKey(FieldName)) htFields.Add(FieldName, i);
                }
                string sDenum = string.Empty, sPretV = string.Empty, sCota = string.Empty, sUm = string.Empty, sCant = string.Empty;

                while (!IsEmptyRow(oSheet, ++iCurrentLine))
                {
                    try
                    {
                        sDenum = getNotNullFieldText(oSheet, iCurrentLine, "DENUMIRE PRODUS");
                        sPretV = getNotNullFieldText(oSheet, iCurrentLine, "PRET UNITAR VANZARE FARA TVA (RON)");
                        sCota = getNotNullFieldText(oSheet, iCurrentLine, "COTA TVA");
                        sCant = getNotNullFieldText(oSheet, iCurrentLine, "CANTITATE");
                        sUm = getNotNullFieldText(oSheet, iCurrentLine, "UM");

                        try
                        {
                            Convert.ToDouble(sPretV);
                        }
                        catch (Exception ex)
                        {
                            sComment = "Campul 'PRET UNITAR VANZARE FARA TVA (RON)', de pe linia " + iCurrentLine.ToString() + " nu contine o valoare valida!";
                            throw new ArgumentException(sComment);
                            //WriteToLog(sLogFile, sComment);
                            //bError = false;
                        }

                        try
                        {
                            Convert.ToInt32(sCota);
                        }
                        catch (Exception ex)
                        {

                            sComment = "Campul 'COTA', de pe linia " + iCurrentLine.ToString() + " nu contine o valoare valida!";
                            throw new ArgumentException(sComment);
                            // WriteToLog(sLogFile, sComment);
                            // bError = false;
                        }

                        try
                        {
                            Convert.ToDouble(sCant);
                        }
                        catch (Exception ex)
                        {
                            sComment = "Campul 'CANTITATE', de pe linia " + iCurrentLine.ToString() + " nu contine o valoare valida!";
                            throw new ArgumentException(sComment);
                            // WriteToLog(sLogFile, sComment);
                            // bError = false;
                        }
                        try
                        {
                            CustomProcedures.conn = connStatic;
                            CustomProcedures.INSERTPRODUSE(string.Empty, sUm, sDenum, Convert.ToInt32(sCota));

                            SqlCommand sqlComm = new SqlCommand("SELECT @@IDENTITY AS NewId", connStatic);
                            SqlDataReader reader = sqlComm.ExecuteReader();
                            int idprod = 0;
                            while (reader.Read())
                            {
                                idprod = Convert.ToInt32(reader["NewId"]);
                            }

                            reader.Dispose();
                            reader.Close();

                            CustomProcedures.INSERTSTOC(idprod, Convert.ToInt32(sCant), Convert.ToDouble(sPretV));


                        }
                        catch { }
                    }
                    catch (Exception ex)
                    {

                        WriteToLog(sLogFile, ex.Message);
                        bError = true;
                    }
                }

                if (bError)
                {
                    if (MessageBox.Show("Importul s-a realizat cu erori! Doriti sa vizualizati fisierul log?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                    {
                        oBook.Close(null, null, null); 
                        oExcel.Workbooks.Close();
                        System.Diagnostics.Process.Start(sLogFile);
                    }
                }
                else MessageBox.Show("Importul s-a realizat cu succes!", "", MessageBoxButtons.OK, MessageBoxIcon.Information);

                if (oSheet != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
                if (oBook != null)
                {
                    oBook.Close(Type.Missing, Type.Missing, Type.Missing);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
                }
                if (oExcel != null) { oExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel); }

                oSheet = null;
                oBook = null;
                oExcel = null;

                GC.Collect();
        }

        public void InitLog(string LogFile)
        {
            try
            {
                if (LogFile.Length > 0)
                {
                    StreamWriter sw = new StreamWriter(LogFile, false);
                    sw.WriteLine(); sw.WriteLine();
                    System.Text.StringBuilder sb = new System.Text.StringBuilder();
                    sb.Append('*', 80); sb.Append(Environment.NewLine);
                    sb.Append(' ', 80); sb.Append(Environment.NewLine);
                    sb.Append("STOC MENTOR, updated " + DateTime.Now.ToLongDateString() + " " + DateTime.Now.ToShortTimeString());
                    sb.Append(Environment.NewLine);
                    sb.Append(' ', 80); sb.Append(Environment.NewLine);
                    sb.Append('*', 80); sb.Append(Environment.NewLine);
                    sw.WriteLine(sb.ToString());
                    sw.Flush(); sw.Close();
                }
            }
            catch { }
        }

        public void WriteToLog(string LogFile, string comment)
        {
            if (LogFile.Length > 0)
            {
                StreamWriter sw = new StreamWriter(LogFile, true);
                sw.WriteLine(comment);
                sw.Flush(); sw.Close();
            }
        }

        private string getNotNullFieldText(Microsoft.Office.Interop.Excel.Worksheet oSheet, int iLine, string FieldName)
        {
            string toReturn = string.Empty;
            toReturn = getFieldText(oSheet, iLine, FieldName);

            string sComment = "Linia " + iLine.ToString() + " contine celula " + FieldName + " necompletata!";
            if (toReturn.Length == 0)
                throw new System.ArgumentNullException(string.Empty, sComment); // Valeur non valide!
            else
                return toReturn;
        }


        private string getFieldText(Microsoft.Office.Interop.Excel.Worksheet oSheet, int iLine, string FieldName)
        {
            string toReturn = string.Empty;
            if (htFields.ContainsKey(FieldName))
            {
                int iCol = Convert.ToInt32(htFields[FieldName]);
                toReturn = getCellText(oSheet, iLine, iCol);
            }
            return toReturn;
        }

        private string getCellHeader(Microsoft.Office.Interop.Excel.Worksheet oSheet, int iLine, int iCol)
        {
            string toReturn = string.Empty;

            try
            {
                Microsoft.Office.Interop.Excel.Range rng;
                rng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[iLine, iCol];

                if (rng.Value2 != null) toReturn = rng.Value2.ToString().Trim();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
            }

            return toReturn;
        }

        private string getCellText(Microsoft.Office.Interop.Excel.Worksheet oSheet, int iLine, int iCol)
        {
            string toReturn = string.Empty;
            bool bParse = false;

            try
            {
                Microsoft.Office.Interop.Excel.Range rng;
                rng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[iLine, iCol];
                if (rng.NumberFormat != null && rng.NumberFormat.ToString().Length > 0)
                {
                    if (rng.NumberFormat.ToString().IndexOf("yy") > 0 || rng.NumberFormat.ToString().IndexOf("aa") > 0)
                    {
                        // DateTime format

                        CultureInfo ci = new CultureInfo("fr-FR", true);
                        if (rng.NumberFormat.ToString().IndexOf("yyyy") > 0)
                        {
                            ci.DateTimeFormat.LongDatePattern = rng.NumberFormat.ToString().Replace("m", "M");
                        }
                        else
                            if (rng.NumberFormat.ToString().IndexOf("yy") > 0)
                            {
                                ci.DateTimeFormat.ShortDatePattern = rng.NumberFormat.ToString().Replace("m", "M");
                            }

                        if (rng.NumberFormat.ToString().IndexOf("aaaa") > 0)
                        {
                            ci.DateTimeFormat.LongDatePattern = rng.NumberFormat.ToString().Replace("m", "M").Replace("a", "y").Replace("j", "d");

                        }
                        else
                            if (rng.NumberFormat.ToString().IndexOf("aa") > 0)
                            {
                                ci.DateTimeFormat.ShortDatePattern = rng.NumberFormat.ToString().Replace("m", "M").Replace("a", "y").Replace("j", "d");

                            }

                        if (rng.Text.ToString().Length > 0)
                            toReturn = DateTime.Parse(rng.Text.ToString(), ci).ToString();
                        else
                            toReturn = DateTime.FromOADate((double)rng.Value2).ToString();
                        bParse = true;
                    }
                }
                if (rng.Value2 != null && !bParse) toReturn = rng.Value2.ToString().Trim();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
            }

            return toReturn;
        }

        private void btnIesire_Click(object sender, EventArgs e)
        {
            ((frmStocuri)formCalling).CallBack();
            this.Close();
        }

    }
}
